package com.bw.dao;

import com.bw.bean.Emp;
import com.bw.bean.Emp1;
import com.bw.bean.Emp2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EmpDao {

    @Select("select \n" +
            "ci.chapter_name `name`,\n" +
            "count(ucp.chapter_id) `num`,\n" +
            "sum(ucp.position_sec) `time`,\n" +
            "count(DISTINCT ucp.user_id) `num1`,\n" +
            "sum(ucp.position_sec)/count(DISTINCT ucp.user_id) `avg`\n" +
            "from user_chapter_process ucp\n" +
            "LEFT JOIN chapter_info ci on ci.id=ucp.chapter_id\n" +
            "group by ci.chapter_name")
    List<Emp1> getList1();

    @Select("select a.name,count(*) `value`from\n" +
            "(select score,\n" +
            "case\n" +
            "when score < 60 then '[0,60)'\n" +
            "when score >= 60 and score < 80 then '[60,80)'\n" +
            "when score >= 80 and score < 90 then '[80,90)'\n" +
            "when score >= 90 and score < 95 then '[90,95)'\n" +
            "when score >= 95 and score <=100 then '[95,100)'\n" +
            "end `name`\n" +
            "from test_exam)a\n" +
            "group by a.name")
    List<Emp> getList2();

    @Select("select \n" +
            "b.name `name`,\n" +
            "sum(o.final_amount) `value`\n" +
            "from order_info o\n" +
            "LEFT JOIN base_province b on o.province_id=b.id\n" +
            "group by b.name")
    List<Emp> getList3();

    @Select("select sum(final_amount) `value` from order_info")
    Double getList4();

    @Select("select hour(create_time) `name`,count(*) `value` from user_chapter_process group by hour(create_time)")
    List<Emp> getList5();

    @Select("select bs.source_site `name`,\n" +
            "sum(o.final_amount) `total`,\n" +
            "count(DISTINCT o.user_id) `num1`,\n" +
            "count(o.id) `num2`\n" +
            "from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id \n" +
            "LEFT JOIN course_info ci ON ci.id=d.course_id \n" +
            "LEFT JOIN chapter_info c ON ci.id=c.course_id \n" +
            "LEFT JOIN video_info vi ON c.id=vi.chapter_id \n" +
            "LEFT JOIN base_source bs ON vi.video_source_id=bs.id \n" +
            "where bs.source_site is not null \n" +
            "GROUP BY bs.source_site")
    List<Emp2> getList6();

    @Select("select\n" +
            "(select count(*) from user_info where create_time='2021-4-23')\n" +
            "-\n" +
            "(select count(*) from user_info where create_time='2021-4-24') `value`")
    Double getList7();

    @Select("select \n" +
            "ci.course_name `name`,\n" +
            "sum(o.final_amount) `value`\n" +
            "from order_info o \n" +
            "LEFT JOIN order_detail d on o.id=d.order_id\n" +
            "LEFT JOIN course_info ci on ci.id=d.course_id\n" +
            "group by ci.course_name\n")
    List<Emp> getList8();

}
